DB Fundamentals

 

 

1-

 

SQL

Database Systems

Personal

Enterprise

 

1-

Structured Query Language (SQL)

Structured Query Language (SQL)  is an international standard for creating, processing and querying databases and their tables.

Many database applications use SQL to retrieve, format, report, insert, delete, and/or modify data for users.

1-

 

SQL Example

1-

• We can use SQL to combine the data in the three tables in the Art Course Database to recreate the original list structure of the data.

• We do this by using an SQL SELECT statement.

 

SQL Example (Cont’d)

1-

SELECT CUSTOMER.CustomerLastName,

 CUSTOMER.CustomerFirstName,

 CUSTOMER.Phone,

 COURSE.CourseDate, ENROLLMENT.AmountPaid,

 COURSE.Course, COURSE.Fee

FROM CUSTOMER, ENROLLMENT, COURSE

WHERE CUSTOMER.CustomerNumber

  = ENROLLMENT.CustomerNumber

  AND COURSE.CourseNumber

  = ENROLLMENT.CourseNumber;

 

SQL Example Results

1-

Figure 1-13: Results of the SQL Query to Recreate the Art Course List

 

Database Systems

The four components of a database system  are:

Users

Database Application

Database Management System (DBMS)

Database

1-

 

Components of a Database System

1-

Figure 1-15: Components of a Database System

 

Users

A  user  of a database system will

use a database application to track things;

use forms to enter, read, delete and query data; and

produce reports.

1-

 

The Database

A database is a self-describing  collection of related  records.

The database itself contains the definition of its structure .

Metadata  is data describing the structure of the database data.

Tables within a relational database are related to each other .

1-

 

Example Database Metadata: A Relationship Diagram

1-

Figure 1:16 Example Metadata: A Relationship Diagram

for the Art Course Tables in Figure 1-10

 

Database Contents

1-

Figure 1-17 Database Contents

 

Database Management System (DBMS)

A  database management system (DBMS)  serves as an intermediary between database applications and the database.

The DBMS manages and controls database activities.

The DBMS creates, processes and administers the databases it controls.

1-

 

Functions of a DBMS

Create databases

Create tables

Create supporting structures

Read database data

Modify database data (insert, update, and delete)

Maintain database structures

Enforce rules

Control concurrency

Provide security

Perform backup and recovery

1-

 

Referential Integrity Constraints

The DBMS will enforce many constraints .

Referential integrity constraints  ensure that the values of a column in one table are valid based on the values in another table.

If a 5 was entered as a CustomerID in the PROJECT table, a Customer having a CustomerID value of 5 must exist in the CUSTOMER table.

1-

 

Database Applications

A  database application  is a set of one or more computer programs that serves as an intermediary between the user and the DBMS.

 

1-

 

Functions of Database Applications

Create and process forms

Process user queries

Create and process reports

Execute application logic

Control database applications

1-

 

Database Applications: Example Data Entry Form

1-

Figure 1-20: Example Data Entry Form

 

Database Applications: Example Query

1-

Figure 1-21: Example Query

 

Database Applications: Example Report

1-

Figure 1-22: Example Report

 

Personal Database Systems

Personal database systems typically

have one application.

have only a few tables.

are simple in design.

involve only one computer.

support one user at a time.

 

1-

 

Personal Database Systems

1-

Figure 1-23: Personal Database System

 

Personal Database Systems: An SQL Query in Microsoft Access

1-

Figure 1-24: SQL Generated by Microsoft Access Query

 

Personal Database Systems: SQL Query Results in Microsoft Access

1-

Enterprise-Class  Database Systems

Enterprise-Class database systems typically:

support several users simultaneously,

include more than one application,

involve multiple computers,

are complex in design,

have many tables, and

have many databases.

 

1-

 

Organizational Database Systems

1-

Figure 1-26 Enterprise-Class Database System

 

Commercial DBMS Products

Example of Desktop DBMS Products

Microsoft Access

Examples of Organizational DBMS Products

Microsoft’s SQL Server

Oracle’s Oracle

Sun Microsystem’s MySQL (Oracle)

IBM’s DB2

1-

 

Enterprise-Class Database Systems: Microsoft SQL Server 2014

1-

Figure 1-27 Microsoft SQL Server 2014

 

Enterprise-Class Database Systems: Oracle Database Express Edition 11 g  Release 2

1-

Figure 1-28 Oracle Database Express Edition 11 g  Release 2

 

Enterprise-Class Database Systems: MySQL 5.6

1-

Figure 1-29: MySQL 5.6